﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using MJM.SCA.EntidadesNegocio;
using MJM.CBD.Conn;

namespace MJM.SCA.AccesoDatos
{
    public class GraficoAD
    {
        public List<GraficoEN> TotalAccesosWS(string FechaIni, string FechaFin,int Tipo)
        {
            List<GraficoEN> lst = new List<GraficoEN>();
            SqlConnection conn = new SqlConnection(Cadena.GetCadenaLog());
            SqlCommand cmd = new SqlCommand("sp_s_T_LOGWS_CLIENT_Report", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter prmFechaInicio, prmFechaFin,prmTipo;
            SqlDataReader dr;
            try
            {
                prmFechaInicio = cmd.CreateParameter();
                prmFechaInicio.ParameterName = "@vFechaIni";
                prmFechaInicio.SqlDbType = SqlDbType.VarChar;
                prmFechaInicio.Value = FechaIni;
                cmd.Parameters.Add(prmFechaInicio);

                prmFechaFin = cmd.CreateParameter();
                prmFechaFin.ParameterName = "@vFechaFin";
                prmFechaFin.SqlDbType = SqlDbType.VarChar;
                prmFechaFin.Value = FechaFin;
                cmd.Parameters.Add(prmFechaFin);

                prmTipo = cmd.CreateParameter();
                prmTipo.ParameterName = "@vTipo";
                prmTipo.SqlDbType = SqlDbType.Int;
                prmTipo.Value = Tipo;
                cmd.Parameters.Add(prmTipo);

                cmd.Connection.Open();
                dr = cmd.ExecuteReader();
                GraficoEN obj;

                while (dr.Read())
                {
                    obj = new GraficoEN();
                    obj.Texto = dr.GetString(0);
                    obj.Cantidad = dr.GetInt32(1);
                    lst.Add(obj);
                }

                cmd.Connection.Close();
                cmd.Dispose();
                conn.Dispose();
            }
            catch(Exception ex)
            {
                cmd.Dispose();
                conn.Dispose();
            }

            return lst;
        }

        public List<GraficoEN> TotalWS()
        {
            List<GraficoEN> lst = new List<GraficoEN>();
            SqlConnection conn = new SqlConnection(Cadena.GetCadenaLog());
            SqlCommand cmd = new SqlCommand("sp_s_T_LOGWS_CLIENT_ListarWS", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader dr;
            try
            {   
                cmd.Connection.Open();
                dr = cmd.ExecuteReader();
                GraficoEN obj;

                while (dr.Read())
                {
                    obj = new GraficoEN();
                    obj.Texto = dr.GetString(0);
               //     obj.Cantidad = dr.GetInt32(1);
                    lst.Add(obj);
                }

                cmd.Connection.Close();
                cmd.Dispose();
                conn.Dispose();
            }
            catch(Exception ex)
            {
                cmd.Dispose();
                conn.Dispose();
            }
            return lst;
        }

        public List<GraficoEN> TotalDiarioWS(string Anio,int Mes,String WebService)
        {
            List<GraficoEN> lst = new List<GraficoEN>();
            GraficoEN obj;
            int pos=0;
            int maxd=0;
            if (Mes == 1 || Mes == 3 || Mes == 5 || Mes == 7 || Mes == 8 || Mes == 10 || Mes == 12)
                maxd=31;
            else if(Mes==4 || Mes==6 || Mes==9 || Mes==11)
                maxd = 30;
            else if(DateTime.IsLeapYear(Convert.ToInt32(Anio)) && Mes==2)
                maxd = 29;
            else
                maxd = 28;
            string dia;
            for (int i = 0; i < maxd; i++)
            {
                obj = new GraficoEN();
                dia=i.ToString();
            /*  if (dia.Length < 2)
                  dia = "0" + dia;*/
                obj.Texto = (i+1).ToString(); 
                obj.Cantidad = 0;
                lst.Add(obj);
            }
            SqlConnection conn = new SqlConnection(Cadena.GetCadenaLog());
            SqlCommand cmd = new SqlCommand("sp_s_T_LOGWS_CLIENT_ListDiaria", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter prmAnio, prmMes,prmWS;
            SqlDataReader dr;
            try
            {
                prmAnio = cmd.CreateParameter();
                prmAnio.ParameterName = "@vAnio";
                prmAnio.SqlDbType = SqlDbType.VarChar;
                prmAnio.Value = Anio;
                cmd.Parameters.Add(prmAnio);

                prmMes = cmd.CreateParameter();
                prmMes.ParameterName = "@vMes";
                prmMes.SqlDbType = SqlDbType.Int;
                prmMes.Value = Mes;
                cmd.Parameters.Add(prmMes);

                prmWS = cmd.CreateParameter();
                prmWS.ParameterName = "@vWebService";
                prmWS.SqlDbType = SqlDbType.VarChar;
                prmWS.Value = WebService;
                cmd.Parameters.Add(prmWS);

                cmd.Connection.Open();
                dr = cmd.ExecuteReader();
              

                while (dr.Read())
                {
                   // obj = new GraficoEN();
                    pos=Convert.ToInt32(dr.GetString(0).Substring(6, 2))-1;
                    lst[pos].Texto = (pos+1).ToString();
                    lst[pos].Cantidad = dr.GetInt32(1);
                    //lst.Add(obj);
                }

                cmd.Connection.Close();
                cmd.Dispose();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                cmd.Dispose();
                conn.Dispose();
            }

            return lst;
        }

        public List<GraficoEN> TotalMetxMes(String Anio, int Mes, String WebService)
        {
            List<GraficoEN> lst = new List<GraficoEN>();
            SqlConnection conn = new SqlConnection(Cadena.GetCadenaLog());
            SqlCommand cmd = new SqlCommand("sp_s_T_LOGWS_CLIENT_ListMet", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter prmAnio, prmMes, prmWS;
            SqlDataReader dr;
            try
            {
                prmAnio = cmd.CreateParameter();
                prmAnio.ParameterName = "@vAnio";
                prmAnio.SqlDbType = SqlDbType.VarChar;
                prmAnio.Value = Anio;
                cmd.Parameters.Add(prmAnio);

                prmMes = cmd.CreateParameter();
                prmMes.ParameterName = "@vMes";
                prmMes.SqlDbType = SqlDbType.Int;
                prmMes.Value = Mes;
                cmd.Parameters.Add(prmMes);

                prmWS = cmd.CreateParameter();
                prmWS.ParameterName = "@vNomServicio";
                prmWS.SqlDbType = SqlDbType.VarChar;
                prmWS.Value = WebService;
                cmd.Parameters.Add(prmWS);

                cmd.Connection.Open();
                dr = cmd.ExecuteReader();

                GraficoEN obj;
                while (dr.Read())
                {
                    obj = new GraficoEN();
                    obj.Texto= dr.GetString(0);
                    obj.Cantidad = dr.GetInt32(1);                    
                    lst.Add(obj);
                }

                cmd.Connection.Close();
                cmd.Dispose();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                cmd.Dispose();
                conn.Dispose();
            }

            return lst;
        }

        public List<GraficoEN> TotalMetDiario(string Anio, int Mes, String NomMetodo)
        {
            List<GraficoEN> lst = new List<GraficoEN>();
            GraficoEN obj;
            int pos = 0;
            int maxd = 0;
            if (Mes == 1 || Mes == 3 || Mes == 5 || Mes == 7 || Mes == 8 || Mes == 10 || Mes == 12)
                maxd = 31;
            else if (Mes == 4 || Mes == 6 || Mes == 9 || Mes == 11)
                maxd = 30;
            else if (DateTime.IsLeapYear(Convert.ToInt32(Anio)) && Mes == 2)
                maxd = 29;
            else
                maxd = 28;
            string dia;
            for (int i = 0; i < maxd; i++)
            {
                obj = new GraficoEN();
                dia = i.ToString();
                /*    if (dia.Length < 2)
                        dia = "0" + dia;*/
                obj.Texto = (i + 1).ToString();
                obj.Cantidad = 0;
                lst.Add(obj);
            }
            SqlConnection conn = new SqlConnection(Cadena.GetCadenaLog());
            SqlCommand cmd = new SqlCommand("sp_s_T_LOGWS_CLIENT_ListMetDiarios", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter prmAnio, prmMes, prmWS;
            SqlDataReader dr;
            try
            {

                prmAnio = cmd.CreateParameter();
                prmAnio.ParameterName = "@vAnio";
                prmAnio.SqlDbType = SqlDbType.VarChar;
                prmAnio.Value = Anio;
                cmd.Parameters.Add(prmAnio);

                prmMes = cmd.CreateParameter();
                prmMes.ParameterName = "@vMes";
                prmMes.SqlDbType = SqlDbType.Int;
                prmMes.Value = Mes;
                cmd.Parameters.Add(prmMes);

                prmWS = cmd.CreateParameter();
                prmWS.ParameterName = "@vNombreMeto";
                prmWS.SqlDbType = SqlDbType.VarChar;
                prmWS.Value = NomMetodo;
                cmd.Parameters.Add(prmWS);

                cmd.Connection.Open();
                dr = cmd.ExecuteReader();


                while (dr.Read())
                {
                    // obj = new GraficoEN();
                    pos = Convert.ToInt32(dr.GetString(0).Substring(6, 2)) - 1;
                    lst[pos].Texto = (pos + 1).ToString();
                    lst[pos].Cantidad = dr.GetInt32(1);
                    //lst.Add(obj);
                }

                cmd.Connection.Close();
                cmd.Dispose();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                cmd.Dispose();
                conn.Dispose();
            }

            return lst;
        }

        public List<GraficoEN> TotalAplxMes(String Anio, int Mes)
        {
            List<GraficoEN> lst = new List<GraficoEN>();
            SqlConnection conn = new SqlConnection(Cadena.GetCadenaLog());
            SqlCommand cmd = new SqlCommand("sp_s_T_LOGAW_CLIENT_ListMes", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter prmAnio, prmMes;
            int a = Convert.ToInt32(Anio);
            SqlDataReader dr;
            try
            {
                prmAnio = cmd.CreateParameter();
                prmAnio.ParameterName = "@vAnio";
                prmAnio.SqlDbType = SqlDbType.Int;
                prmAnio.Value = a;
                cmd.Parameters.Add(prmAnio);

                prmMes = cmd.CreateParameter();
                prmMes.ParameterName = "@vMes";
                prmMes.SqlDbType = SqlDbType.Int;
                prmMes.Value = Mes;
                cmd.Parameters.Add(prmMes);              

                cmd.Connection.Open();
                dr = cmd.ExecuteReader();

                GraficoEN obj;
                while (dr.Read())
                {
                    obj = new GraficoEN();
                    obj.Texto = dr.GetString(1);
                    obj.Cantidad = dr.GetInt32(0);
                    lst.Add(obj);
                }

                cmd.Connection.Close();
                cmd.Dispose();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                cmd.Dispose();
                conn.Dispose();
            }

            return lst;
        }

        public List<GraficoEN> TotalAplDiario(string Anio, int Mes, String NomMetodo)
        {
            List<GraficoEN> lst = new List<GraficoEN>();
            GraficoEN obj;
            int pos = 0;
            int maxd = 0;
            if (Mes == 1 || Mes == 3 || Mes == 5 || Mes == 7 || Mes == 8 || Mes == 10 || Mes == 12)
                maxd = 31;
            else if (Mes == 4 || Mes == 6 || Mes == 9 || Mes == 11)
                maxd = 30;
            else if (DateTime.IsLeapYear(Convert.ToInt32(Anio)) && Mes == 2)
                maxd = 29;
            else
                maxd = 28;
            string dia;
            for (int i = 0; i < maxd; i++)
            {
                obj = new GraficoEN();
                dia = i.ToString();
                /*    if (dia.Length < 2)
                        dia = "0" + dia;*/
                obj.Texto = (i + 1).ToString();
                obj.Cantidad = 0;
                lst.Add(obj);
            }
            SqlConnection conn = new SqlConnection(Cadena.GetCadenaLog());
            SqlCommand cmd = new SqlCommand("sp_s_T_LOGAW_CLIENT_ListDiario", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter prmAnio, prmMes, prmWS;
            SqlDataReader dr;
           
            try
            {

                prmAnio = cmd.CreateParameter();
                prmAnio.ParameterName = "@vAnio";
                prmAnio.SqlDbType = SqlDbType.VarChar;
                prmAnio.Value = Anio;
                cmd.Parameters.Add(prmAnio);

                prmMes = cmd.CreateParameter();
                prmMes.ParameterName = "@vMes";
                prmMes.SqlDbType = SqlDbType.Int;
                prmMes.Value = Mes;
                cmd.Parameters.Add(prmMes);

                prmWS = cmd.CreateParameter();
                prmWS.ParameterName = "@vAplicacion";
                prmWS.SqlDbType = SqlDbType.VarChar;
                prmWS.Value = NomMetodo;
                cmd.Parameters.Add(prmWS);

                cmd.Connection.Open();
                dr = cmd.ExecuteReader();


                while (dr.Read())
                {
                    // obj = new GraficoEN();
                    pos = Convert.ToInt32(dr.GetString(0).Substring(6, 2)) - 1;
                    lst[pos].Texto = (pos + 1).ToString();
                    lst[pos].Cantidad = dr.GetInt32(1);
                    //lst.Add(obj);
                }

                cmd.Connection.Close();
                cmd.Dispose();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                cmd.Dispose();
                conn.Dispose();
            }

            return lst;
        }

        public List<GraficoEN> ListarDelitos()
        {
            List<GraficoEN> lst = new List<GraficoEN>();
            SqlConnection conn = new SqlConnection(Cadena.GetCadenaObservatorio());
            SqlCommand cmd = new SqlCommand("sp_s_T_MAES_DELI_ListDeslitos", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader dr;
            GraficoEN obj;
            try
            {
                cmd.Connection.Open();
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    obj = new GraficoEN();
                    obj.Cantidad = dr.GetInt32(0);
                    obj.Texto = dr.GetString(1);
                    obj.Texto2 = dr.GetString(2);
                    lst.Add(obj);
                }
            }
            catch(Exception ex)
            {
                cmd.Dispose();
                conn.Dispose();
            }
            return lst;
        }

        public List<GraficoEN> GraficoDelito(int Delito, String Anio,int idZona)
        {
            List<GraficoEN> lst = new List<GraficoEN>();
            SqlConnection conn = new SqlConnection(Cadena.GetCadenaObservatorio());
            SqlCommand cmd = new SqlCommand("sp_s_T_REG_INCI_CantIncidencias", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter prmIdDelito, prmAnio,prmZona;
            SqlDataReader dr;
            GraficoEN obj;
            for (int i = 0; i < 12; i++)
            {
                obj = new GraficoEN();
                obj.Texto = (i+1).ToString();
                obj.Cantidad = 0;
                lst.Add(obj);
            }
            int pos;
            //[sp_s_T_REG_INCI_CantIncidencias]
            try
            {
                prmAnio = cmd.CreateParameter();
                prmAnio.ParameterName = "@vAnio";
                prmAnio.SqlDbType = SqlDbType.VarChar;
                prmAnio.Value = Anio;
                cmd.Parameters.Add(prmAnio);

                prmZona = cmd.CreateParameter();
                prmZona.ParameterName = "@vSEC_P_inID";
                prmZona.SqlDbType = SqlDbType.Int;
                prmZona.Value = idZona;
                cmd.Parameters.Add(prmZona);

                prmIdDelito = cmd.CreateParameter();
                prmIdDelito.ParameterName = "@vDELI_P_inID";
                prmIdDelito.SqlDbType = SqlDbType.Int;
                prmIdDelito.Value = Delito;
                cmd.Parameters.Add(prmIdDelito);

                cmd.Connection.Open();
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    pos = Convert.ToInt32(dr.GetString(0));
                    lst[pos-1].Cantidad=dr.GetInt32(1);
                }

                cmd.Connection.Close();
                cmd.Dispose();
                conn.Dispose();
            }
            catch(Exception ex)
            {
                cmd.Dispose();
                conn.Dispose();
            }

            return lst;
        }

        public List<GraficoEN> ListarTipoDeli()
        {
            List<GraficoEN> lst = new List<GraficoEN>();
            SqlConnection conn = new SqlConnection(Cadena.GetCadenaObservatorio());
            SqlCommand cmd = new SqlCommand("sp_s_T_MAES_DELI_TIPO_ListTipo", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader dr;
            GraficoEN obj;
            try
            {
                cmd.Connection.Open();
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    obj = new GraficoEN();
                    obj.Cantidad = dr.GetInt32(0);
                    obj.Texto = dr.GetString(1);
                    lst.Add(obj);
                }
                cmd.Connection.Close();
                cmd.Dispose();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                cmd.Dispose();
                conn.Dispose();
            }
            return lst;
        }

        public List<GraficoEN> ListarCantidadIncidencia(String FechaInicio, String FechaFin)
        {
            List<GraficoEN> lst = new List<GraficoEN>();
            SqlConnection conn = new SqlConnection(Cadena.GetCadenaObservatorio());
            SqlCommand cmd = new SqlCommand("sp_s_T_REG_INCI_CantFecha", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader dr;
            GraficoEN obj;
            SqlParameter prmFecIni, prmFecFin;
            try
            {
                prmFecIni = cmd.CreateParameter();
                prmFecIni.ParameterName = "@vFechaInicio";
                prmFecIni.SqlDbType = SqlDbType.VarChar;
                prmFecIni.Value = FechaInicio;
                cmd.Parameters.Add(prmFecIni);

                prmFecFin = cmd.CreateParameter();
                prmFecFin.ParameterName = "@vFechaFin";
                prmFecFin.SqlDbType = SqlDbType.VarChar;
                prmFecFin.Value = FechaFin;
                cmd.Parameters.Add(prmFecFin);
                
                cmd.Connection.Open();
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    obj = new GraficoEN();
                    obj.Texto2 = dr.GetInt32(0).ToString();
                    obj.Cantidad = dr.GetInt32(2);
                    obj.Texto = dr.GetString(1);
                    lst.Add(obj);
                }

                cmd.Connection.Close();
                cmd.Dispose();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                cmd.Dispose();
                conn.Dispose();
            }
            return lst;
        }

        public List<GraficoEN> ListarZonas()
        {
            SqlConnection conn = new SqlConnection(Cadena.GetCadenaObservatorio());
            SqlCommand cmd = new SqlCommand("sp_s_WEB_T_MAES_SECTOR_Listar", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader dr;
            List<GraficoEN> lst = new List<GraficoEN>();
            GraficoEN obj;
            try
            {
                cmd.Connection.Open();
                dr = cmd.ExecuteReader();
                while (dr.Read())
                { 
                    obj=new GraficoEN();
                    obj.Texto = dr.GetInt32(0).ToString();
                    obj.Texto2 = dr.GetString(1);
                    lst.Add(obj);
                }
                cmd.Connection.Close();
                cmd.Dispose();
                conn.Dispose();
            }
            catch(Exception ex)
            {                
                cmd.Dispose();
                conn.Dispose();
            }
            return lst;
        }
    }
}
